"""
导出zabbix中资源使用第三个版本。
在第三个版本中，指定一个时间范围，可以获取CPU、内存、网络监控项在这个时间范围内的最小值、最大值和他们之间的差。cpu和内存数值均省略了百分比符号，单位采用的都是百分比制。

1. 从这几种参数中寻找使用率低的虚拟机
   1) cpu波动小
   2) mem波动小
   3) 磁盘波动小
   4) 网络波动小
2. 使用trends表比使用history更效率，如果是取7天数据trends只需要24 * 7条数据，而history要60 * 24 * 7条数据。
3. 对于取trends中的数据可以对每条需要取的数据同时获取最低值和最大值，然后求值他们之间的差值，插值越小越可能是不在使用的虚拟机。

特性：
1.只获取开启状态的虚拟机。
2.对部分没有数据的监控项进行数据填充。
3.可以从excel表格指定虚拟机清单，也可以从数据库自定虚拟机清单。

验证：
目前可以从指定表格中获取数据进行查询。
"""
import abc
import time
from mytool.zabbix.data_export import database
from mytool.system.date import MyDate
from mytool.office.excel import SingleExcel

db = database.DB()
table_mapping = {
    0: 'trends',        # float
    3: 'trends_uint'    # int
}

# 这两个参数用于指定历史数据的时间范围，start_time的时间等于end_time - start_time.
start_time = 60 * 60 * 24
end_time = None


class Host:
    def __init__(self):
        self.host_info = """select h.name, h.host, i.ip from hosts h
                        join interface i on h.hostid = i.hostid
                        where h.hostid = {}
                        """
        self.single_hostid = """select h.name, h.hostid, h.status, i.ip from hosts h
                            join interface i on h.hostid = i.hostid
                            where h.hostid = '{}'"""
        self.single_hostname = """select h.name, h.hostid, h.status, i.ip from hosts h
                            join interface i on h.hostid = i.hostid
                            where h.name = '{}'"""
        self.all_hosts = """select hg.hostid, g.name as groupname, g.groupid from hosts_groups hg
                        join groups g on hg.groupid = g.groupid
                        where g.name regexp '[0-9]_'"""
                        # where g.name regexp '[0-9]_'"""

    def get_all_hosts(self):
        return db.query(self.all_hosts)

    def get_single_host(self, host):
        return db.query(self.single_hostid.format(host))

    def get_host_info(self, hostid):
        _host_info = db.query(self.host_info.format(hostid))
        _host_info = _host_info[0]
        if _host_info:
            return [_host_info['name'], _host_info['ip']]
        else:
            assert ValueError


class History(metaclass=abc.ABCMeta):
    """
    历史数据需要提供一个取样范围，这个时间取样仅仅是通过limit来获取范围内的值，而不是通过时间范围来取值。
    """
    def __init__(self):
        """
        region是一个取样范围。
        """
        self.sqls = []
        self.trends = """select clock, value_min, value_max from {} where itemid = {} and clock > {} and clock < {}"""
        self.table_name = None

    def get_history(self, hostid):
        host_item = []
        history_data = []

        for sql in self.sqls:
            _host_item = db.query(sql.format(hostid))
            if not _host_item:
                continue
            host_item.append(_host_item[0])
            self.table_name = table_mapping[_host_item[0]['value_type']]
            query_data = db.query(self.trends.format(self.table_name, _host_item[0]['itemid'], start_time, end_time))
            if query_data:
                history_data.append(query_data)
        return self._data_handle(host_item, history_data)

    @abc.abstractmethod
    def _data_handle(self, item_variety, _history):
        pass

    def _round(self, _history):
        """
        else中进行单位换算。
        """
        if isinstance(_history, list):
            rounded_history = []
            for per in _history:
                rounded_history.append(round(per, 2))
            return rounded_history
        else:
            unit = {
                0: '',
                1: 'KB',
                2: 'MB',
                3: 'GB',
                4: 'TB'
            }
            for i in range(4):
                if _history > 1024:
                    _history = _history / 1024
                else:
                    break
            return str(round(_history, 2)) + unit[i]


class MemHistory(History):
    """
    内存历史数据已实现取样范围。
    """
    def __init__(self):
        super().__init__()
        self.used_mem_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vm.memory.size.*(pav|pused)' and hostid = {}"""
        self.sqls = [self.used_mem_itemid]

    def _data_handle(self, itemid, history_data):
        """
        内存取样数据全部按照已使用来进行输出。
        """
        values = []

        if not history_data:
            return [0, 0, 0]

        key = itemid[0]['key_']
        for data in history_data:
            min_data = []
            max_data = []
            for every_data in data:
                if 'pavailable' in key:
                    min_data.append(100 - every_data['value_min'])
                    max_data.append(100 - every_data['value_max'])
                elif 'pused' in key:
                    min_data.append(every_data['value_min'])
                    max_data.append(every_data['value_max'])
            r_min = round(min(min_data), 2)
            r_max = round(max(max_data), 2)
            values.append(r_min)
            values.append(r_max)
            values.append(abs(r_max - r_min))

        return values


class DiskHistory(History):
    def __init__(self):
        super().__init__()
        self.used_disk_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vfs.fs.size.*,used' and hostid = {}"""
        self.total_disk_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vfs.fs.size.*,total' and hostid = {}"""
        self.sqls = [self.used_disk_itemid, self.total_disk_itemid]
        # self.trends = """select clock, value_max from {} where itemid = {} order by clock desc limit 1"""

    def _data_handle(self, itemid, _history):
        total = []
        for _item_variety in itemid:
            _sum = 0
            for _item in _item_variety:
                key_ = _item['key_']
                if len(key_.split('/')) > 2:    # 盘符超过2个以上的/就不是我们需要的监控项，直接跳过。
                    continue
                disk = db.query(self.trends.format(self.table_name, _item['itemid']))
                if not disk:
                    continue
                disk = disk[0] if disk else disk
                if disk['clock'] > end_time:
                    _sum += int(disk['value_max'])
            _sum = round(_sum / 1024 / 1024 / 1024, 2)
            total.append(_sum)
        return total


class CPUHistory(History):
    def __init__(self):
        super().__init__()
        self.win_cpu = """select itemid, key_, value_type from items
                        where key_ = 'system.cpu.load[all,avg1]' and hostid = {}"""
        self.linux_cpu = """select itemid, key_, value_type from items
                        where key_ like 'perf_counter%Processor%' and hostid = {}"""
        self.sqls = [self.win_cpu, self.linux_cpu]

    def _data_handle(self, itemid, history_data):
        values = []
        if not history_data:
            return [0, 0, 0]

        for data in history_data:
            min_data = []
            max_data = []
            for every in data:
                min_data.append(every['value_min'])
                max_data.append(every['value_max'])
            r_min = round(min(min_data), 2)
            r_max = round(max(max_data), 2)
            values.append(r_min)
            values.append(r_max)
            values.append(r_max - r_min)

        return values


class IntHistory(History):
    def __init__(self):
        super().__init__()
        self.win_in_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ like 'perf_counter%Network%Received%'"""
        self.win_out_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ like 'perf_counter%Network%Sent%'"""
        self.linux_in_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ regexp 'net.if.in.(ens.*|eth.*|eno.*)' and flags = 4"""
        self.linux_out_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ regexp 'net.if.out.(ens.*|eth.*|eno.*)' and flags = 4"""
        self.if_total_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ regexp 'net.if.total.(ens.*|eth.*|eno.*)' and flags = 4"""
        self.sqls = [self.win_in_itemid, self.win_out_itemid, self.linux_in_itemid, self.linux_out_itemid]

    def _data_handle(self, itemid, history_data):
        values = []
        if not history_data:
            return [0, 0, 0, 0, 0, 0]

        for data in history_data:
            min_data = []
            max_data = []
            for every in data:
                min_data.append(every['value_min'])
                max_data.append(every['value_max'])
            r_min = round(min(min_data), 2)
            r_max = round(max(max_data), 2)
            values.append(r_min)
            values.append(r_max)
            values.append(r_max - r_min)
        return values


def main(excel, vms):
    cpu_hist = CPUHistory()
    mem_hist = MemHistory()
    int_hist = IntHistory()
    hosts = Host()
    have_data = []
    no_data = []
    have_data.append(['虚拟机名', 'IP地址', 'CPU最小值', 'CPU最大值', 'CPU波动差', '内存最小值', '内存最大值', '内存波动差',
                     '网络接收最小值', '网络接收最大值', '网络接收波动差', '网络发送最小值', '网络发送最大值', '网络发送波动值'])
    for n, vm_name in enumerate(vms):
        every_cpu = []
        _cpu_statistic = []
        # _hostid = hosts.get_single_host(vm_name['hostid'])
        # print(_hostid)
        # 放弃获取禁用的虚拟机。
        if len(vm_name) < 2:
            no_data.append([vm_name['name']])

            continue
        hostid = vm_name['hostid']

        for info in hosts.get_host_info(hostid):
            every_cpu.append(info)
            _cpu_statistic.append(info)
        every_cpu += cpu_hist.get_history(hostid)
        every_cpu += mem_hist.get_history(hostid)
        every_cpu += int_hist.get_history(hostid)
        have_data.append(every_cpu)

    excel.write_all_sheet(have_data, 'CPU内存网络历史数据')
    excel.write_all_sheet(no_data, 'no_history')


def from_excel():
    """
    从excel表格中读取虚拟机清单。
    """
    vm_data = []
    host = Host()
    se = SingleExcel(read_path='f:\\电子认证和信用芜湖.xlsx', read_column='f', set_read_sheet='Sheet1', start=2)
    for name in se.data:
        value = db.query(host.single_hostname.format(name[0]))
        if len(value) == 0:
            vm_data.append({'name': name[0]})
            continue
        # if len(value) == 0:
        #     continue
        vm_data.append(value[0])
    # vm_data = [{'hostid': 13974}]
    return vm_data


def from_database():
    """
    从数据库中获取虚拟机清单。
    """
    host = Host()
    return host.get_all_hosts()


if __name__ == '__main__':
    end_time = int(time.time())
    start_time = end_time - start_time
    vm_data = from_excel()
    mydate = MyDate()
    file_date = str(mydate.month()) + '-' + str(mydate.day())
    file_name = 'export_zabbix_{}.xlsx'.format(file_date)
    path = 'f:\\'
    excel = SingleExcel(write_path=path + file_name)
    excel.add_sheet('CPU内存网络历史数据')
    excel.add_sheet('no_history')
    # end_time = mydate - '4h'
    print(end_time - start_time)
    print(end_time)
    main(excel, vm_data)
